/*
 * Copyright (c) $today.year-Now http://www.zxpnet.com All rights reserved.
 */

package com.zxp.common.codegen.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Maps;
import com.zxp.common.codegen.constant.DbType;
import com.zxp.common.codegen.pojo.Column;
import com.zxp.common.codegen.pojo.DataBase;
import com.zxp.common.codegen.pojo.Table;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.dom4j.Document;
import org.dom4j.Element;

import java.sql.*;
import java.util.*;

/**
 * @desc:
 * @author: shollin
 * @date: 2021/6/17/017 9:19
 */
@Slf4j
public class DataBaseUtil {

    // mysql自带的数据库
    private String[] mysql_system_database = new String[]{"performance_schema", "mysql", "information_schema", "sys"};

    public static Map<String , Connection> connectionMap = Maps.newConcurrentMap();

    //获取到mysql中所有的数据库名称
    //获取数据库连接
    @SneakyThrows
    public static Connection getConnection(DataBase db) {

        String url = db.getUrl();
        Connection connection = connectionMap.get(url);
        if(connection!=null){
            return connection;
        }

        Properties props = new Properties();
        props.put("remarksReporting", "true");//获取数据库的备注信息
        props.put("user", db.getUsername());
        props.put("password", db.getPassword());
        Class.forName(db.getDbType().getDriverClass());//注册驱动
        connection = DriverManager.getConnection(url, props);
        connectionMap.put(url,connection); //缓存起来
        return connection;
    }

    @SneakyThrows
    public static void closeConnection(DataBase db){
        Connection connection = getConnection(db);
        log.debug("关闭："+connection);
        if(connection!=null){
            connection.close();
            connectionMap.remove(db.getUrl());
        }
    }

    public static List<String> getSchemas(DataBase db) {
        return getSchemas(db, false);
    }

    /**
     * 获取数据库列表,不包括系统默认数据库
     *
     * @param db
     * @return
     */
    @SneakyThrows
    public static List<String> getSchemas(DataBase db, Boolean containSysDB) {
        //1.获取元数据
        Connection connection = getConnection(db);
        DatabaseMetaData metaData = connection.getMetaData();
        //2.获取所有数据库列表
        ResultSet rs = metaData.getCatalogs();
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            String database = rs.getString(1);
            if (containSysDB != null && containSysDB) { //包含数据库系统里面的数据库
                list.add(database);
            } else {
                // 排队mysql里面的默认数据库
                if (DbType.MYSQL.equals(db.getDbType()) && !StrUtil.containsAny(database, "performance_schema", "mysql", "information_schema", "sys")) {
                    list.add(database);
                    // 排除 oracle默认数据库
                } else if (DbType.ORACLE.equals(db.getDbType())) {
                    list.add(database);
                } else {
                    list.add(database);
                }
            }
        }
        rs.close();
        return list;
    }

    /**
     * 获取数据库中的表和字段构造实体类
     *      Table对象
     *
     *  1.参数
     *      DataBase 数据库对象
     *  2.操作步骤
     *      1.获取连接
     *      2.获取DatabaseMetaData
     *      3.获取当前数据库中的所有表
     *      4.获取每个表中的所有字段
     *      5.封装到java对象中即可
     *
     * @param db
     * @param tablePrefix  表前缀
     * @param convertMap : 类型转换匹配的map
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @SneakyThrows
    public static List<Table> getDbInfo(DataBase db,String[] tablePrefix, Map<String, String> convertMap1, String[] tables) {

        if(tablePrefix==null){
            String prefix = PropertiesUtils.customMap.get("tableRemovePrefixes");
            if(StrUtil.isNotBlank(prefix)){
                tablePrefix = prefix.split(",");
            }
        }

        // 合并默认数据模型 和 传入的数据模型
        Map<String, String> convertMap = new HashMap<String, String>();
        convertMap.putAll(PropertiesUtils.customMap);
        if(ObjectUtil.isNotEmpty(convertMap1)){
            convertMap.putAll(convertMap1);
        }

        String userName = db.getUsername();

        Connection connection = getConnection(db);
        DatabaseMetaData metaData = connection.getMetaData();

        String schema = null;
        String catalog = connection.getCatalog();

        //如果是oracle数据库
        if (DbType.ORACLE.equals(db.getDbType())) {
            schema = userName.toUpperCase();
        }

        ResultSet tablers = metaData.getTables(catalog, schema, null, new String[]{"TABLE"});

        List<Table> list = new ArrayList<Table>();
        while (tablers.next()) {
            Table table = new Table();
            String tableName = tablers.getString("TABLE_NAME");

            //如果为垃圾表
            if (tableName.indexOf("=") >= 0 || tableName.indexOf("$") >= 0) {
                continue;
            }

            // 只生成表数组里面的表
            if(tables!=null && tables.length>0){
                boolean isIn = false;
                for (String t : tables) {
                    if( StrUtil.equalsIgnoreCase(tableName,t) ){
                        isIn = true;
                        break; // 跳出for循环
                    }
                }
                if( !isIn){
                    continue; // 不包括的话，则while下一个
                }
            }

            //i.表名
            //判断 表名为全大写 ，则转换为小写
            if (tableName.toUpperCase().equals(tableName)) {
                table.setName(tableName.toLowerCase());
            } else {
                table.setName(tableName);
            }

            //ii.类名: 去掉表前缀, 转驼峰写法
            String className = removePrefix(tableName,tablePrefix);//User
            table.setName2(className);

            //iii.描述
            table.setComment(tablers.getString("REMARKS"));

            //iv.主键 获得主键
            ResultSet primaryKeys = metaData.getPrimaryKeys(catalog, schema, tableName);
            List<String> keys = new ArrayList<String>();
            while (primaryKeys.next()) {
                String keyname = primaryKeys.getString("COLUMN_NAME");
                //判断 表名为全大写 ，则转换为小写
                if (keyname.toUpperCase().equals(keyname)) {
                    keyname = keyname.toLowerCase();//转换为小写
                }
                keys.add(keyname);
            }
            table.setKey( StrUtil.join(",", keys));

            //处理表中的所有字段
            log.debug("数据库：" + catalog + " schema:  " + schema + " 表名:  " + tableName);
            //获得所有列
            ResultSet columnrs = metaData.getColumns(catalog, schema, tableName, null);

            List<Column> columnList = new ArrayList<Column>();
            while (columnrs.next()) {
                Column column = new Column();
                String columnName = columnrs.getString("COLUMN_NAME");
                //判断 列名为全大写 ，则转换为小写
                if (columnName.toUpperCase().equals(columnName)) {
                    columnName = columnName.toLowerCase();//转换为小写
                }
                column.setColumnName(columnName);

                //属性名
                String attName = StringUtils.toJavaVariableName(columnName);
                column.setColumnName2(attName);

                //java类型和数据库类型
                //这里有可能有出现 INT UNSIGNED(无符号),所以只取前面的INT就可以了
                String columnDbType = columnrs.getString("TYPE_NAME").split(" ")[0];//VARCHAR,DATETIME

                // 在mybatis的org.apache.ibatis.type.JdbcType枚举当中，没有DATETIME、INT、TEXT、MEDIUMINT这一些jdbcType，因此需要进行替换
                if (columnDbType.equals("DATETIME")){
                    columnDbType = "TIMESTAMP";
                }
                if (columnDbType.equals("INT")) {
                    columnDbType = "INTEGER";
                }
                if (columnDbType.equals("TEXT")) {
                    columnDbType = "VARCHAR";
                }
                if (columnDbType.equals("MEDIUMINT")) {
                    columnDbType = "INTEGER";
                }

                column.setColumnDbType(columnDbType);//数据库原始类型

                String typeName = convertMap.get(columnDbType);//获取转换后的类型
                if (typeName == null) {
                    typeName = columnDbType;
                }
                column.setColumnType(typeName);

                String remarks = columnrs.getString("REMARKS");//备注
                if (remarks == null) {
                    remarks = columnName;
                }
                column.setColumnComment(remarks);

                if (keys.contains(columnName))//如果该列是主键
                {
                    column.setColumnKey("PRI");
                    table.setKey(column.getColumnName());
                } else {
                    column.setColumnKey("");
                }
                int decimal_digits = columnrs.getInt("DECIMAL_DIGITS");//小数位数
                if (decimal_digits > 0) {
                    column.setColumnType("Double");//如果是小数则设置为Double
                }

                column.setDecimalDigits(decimal_digits);//
                column.setColumnSize(columnrs.getInt("COLUMN_SIZE"));//字段长度

                columnList.add(column);
            }
            columnrs.close();
            table.setColumns(columnList);

            list.add(table);

        }
        tablers.close();
        return list;
    }

    public static String removePrefix(String tableName, String[] tablePrefix) {
        //String prefix = PropertiesUtils.customMap.get("tableRemovePrefixes");
        //bs_,     tb_    , co_    ,
        String temp = tableName;  //bs_user
        for(String pf : tablePrefix) {
            temp = StringUtils.removePrefix(temp,pf,true);
        }
        //temp = user
        return StringUtils.makeAllWordFirstLetterUpperCase(temp);
    }

    private Map<String, Object> xmlDataToMap(Document doc, String attrKey, String attrValue) {
        HashMap<String, Object> map = Maps.newHashMap();

        Element rootElement = doc.getRootElement();
        List<Element> elements = rootElement.elements();
        for (Element element : elements) {
            map.put(element.attributeValue(StrUtil.blankToDefault(attrKey, "key")), element.attributeValue(StrUtil.blankToDefault(attrValue, "value")));
        }
        return map;
    }


    public static void main(String[] args) {
        DataBase dataBase = new DataBase(DbType.MYSQL, "localhost", "jeesite");
        dataBase.setUsername("root");
        dataBase.setPassword("123456");

        List<String> databases = getSchemas(dataBase);
        for (String database : databases) {
           // System.out.println(database);
        }
        HashMap<String, String> map = Maps.newHashMap();
        String[] tablePrefix = {"js_"};
        map.put("VARCHAR","String");

        String[] tables = {"js_sys_company"};

        List<Table> tablesList = getDbInfo(dataBase, tablePrefix,null,tables);

        for (Table table : tablesList) {
            System.out.println(table);
        }

    }
}
